﻿using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using Production.CommonLib.Models;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace Production.ApplicationLib.Queries
{
    public class ProductionOrderQueries : IProductionOrderQueries
    {
        private readonly string? _defaultConnStr;

        public ProductionOrderQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }
        public async Task<PaginatedResult<ProductionOrderInfoResponseDto>?> QueryProductionOrdersByPage(ProductionOrderSearchRequestDto model)
        {
            IEnumerable<ProductionOrderInfoResponseDto>? data;
            int totalCount;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       ordersource,
       orderstatus,
       standardbomid,
       standardbomcode,
       standardbomname,
       quantity,
       expectedtime,
       endtime,
       planstarttime,
       planendtime,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_PRODUCTIONORDER t where 1 = 1 ");

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<ProductionOrderInfoResponseDto>(query.SqlPage);

                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount);
            }

            return await PaginatedResult<ProductionOrderInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }

        public async Task<Result<IList<ProductionOrderInfoResponseDto>>?> QueryProductionOrders(ProductionOrderSearchRequestDto model)
        {
            IEnumerable<ProductionOrderInfoResponseDto>? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryAsync<ProductionOrderInfoResponseDto>(@"
select id,
       code,
       name,
       ordersource,
       orderstatus,
       standardbomid,
       standardbomcode,
       standardbomname,
       quantity,
       expectedtime,
       endtime,
       planstarttime,
       planendtime,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_PRODUCTIONORDER t where 1 = 1 ");
            }

            return await Result<IList<ProductionOrderInfoResponseDto>>.SuccessAsync(data.ToList());

        }
        
        public async Task<Result<ProductionOrderInfoResponseDto>?> GetProductionOrder(int id)
        {
            ProductionOrderInfoResponseDto? data;

            using (var db = new SqlConnection(_defaultConnStr))
            {
                data = await db.QueryFirstOrDefaultAsync<ProductionOrderInfoResponseDto>(@"
select id,
       code,
       name,
       ordersource,
       orderstatus,
       standardbomid,
       standardbomcode,
       standardbomname,
       quantity,
       expectedtime,
       endtime,
       planstarttime,
       planendtime,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_PRODUCTIONORDER t where 1 = 1 and id = @id ", new { id });
            }

            return await Result<ProductionOrderInfoResponseDto>.SuccessAsync(data);
        }
    }
}